package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.COADataList;
import com.mbc.rmt.entities.COA;
import com.mbc.rmt.entities.COACurrency;
import com.mbc.rmt.entities.COARpt;

public class COADAOImpls extends DAO<COA> implements COADAO {

	public COADAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "COA";
	}

	@Override
	public void create(COA entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( coakey, ac_id, description, departmentkey, actype, acgroup, post_in, acstatus, createddate, updateddate, createduser, updateduser, cashflow, default_currency, branchId) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getCOAKey());
		pstmt.setString(i++, entity.getAC_ID());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getDepartmentKey());
		pstmt.setLong(i++, entity.getACType());
		pstmt.setLong(i++, entity.getACGroup());
		pstmt.setString(i++, entity.getPost_In());
		pstmt.setString(i++, entity.getACStatus());
		pstmt.setString(i++, entity.getCreatedDate());
		pstmt.setString(i++, entity.getUpdatedDate());
		pstmt.setLong(i++, entity.getCreatedUser());
		pstmt.setLong(i++, entity.getUpdatedUser());
		pstmt.setString(i++, entity.getCashFlow());
		pstmt.setLong(i++, entity.getDefault_Currency());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void update(COA entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set "
				+ " ac_id = ?, description = ?, departmentkey = ?, actype = ?, acgroup = ?, post_in = ?, acstatus = ?, createddate = ?, updateddate = ?, createduser = ?, updateduser = ?, cashflow = ?, default_currency = ?, branchId = ?"
				+ " where coakey = " + entity.getCOAKey();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getAC_ID());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getDepartmentKey());
		pstmt.setLong(i++, entity.getACType());
		pstmt.setLong(i++, entity.getACGroup());
		pstmt.setString(i++, entity.getPost_In());
		pstmt.setString(i++, entity.getACStatus());
		pstmt.setString(i++, entity.getCreatedDate());
		pstmt.setString(i++, entity.getUpdatedDate());
		pstmt.setLong(i++, entity.getCreatedUser());
		pstmt.setLong(i++, entity.getUpdatedUser());
		pstmt.setString(i++, entity.getCashFlow());
		pstmt.setLong(i++, entity.getDefault_Currency());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where coakey = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	public COA find(long key) throws SQLException {
		COA l_COA = null;
		query = getSelectQuery() + " where coakey = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_COA = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_COA;
	}

	@Override
	public COA findCOAByCode(String code, long branchId) throws SQLException {
		COA l_COA = null;
		query = " select row_number() over(order by ac_id desc) as [rowno], coa.*, d.departmentCode, d.departmentID "
				+ "from COA coa INNER JOIN Department d on coa.DepartmentKey = d.departmentID "
				+ " where AC_ID = '" + code + "' and coa.BranchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_COA = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_COA;
	}

	@Override
	public ArrayList<COA> findAll() throws SQLException {
		ArrayList<COA> l_COAList = new ArrayList<COA>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_COAList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_COAList;
	}

	@Override
	public COADataList findCOAs(COA cri) throws SQLException {
		COADataList l_DataList = new COADataList();
		ArrayList<COA> l_entityList = new ArrayList<COA>();
		String l_cri = " and " + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%'"
//				" and (ac_id like N'%" + cri.getKeyword() + "%'"
//				+ " or COA.description like N'%" + cri.getKeyword() + "%')"
				+ " and COA.BranchId = " + cri.getBranchId();
		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by ac_id desc) as [rowno], coa.*, d.departmentCode, d.departmentID "
				+ "from COA coa INNER JOIN Department d on coa.DepartmentKey = d.departmentID "
				+ " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(AC_ID) from " + table_name + " where 1 = 1"
				+ l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected COA readRecord(ResultSet rs) throws SQLException {
		COA l_entity = new COA();
		l_entity.setCOAKey(rs.getLong("COAKey"));
		l_entity.setAC_ID(rs.getString("AC_ID"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setDepartmentKey(rs.getLong("DepartmentKey"));
		l_entity.setDepartmentid(rs.getString("departmentCode"));
		l_entity.setACType(rs.getInt("ACType"));
		l_entity.setACGroup(rs.getInt("ACGroup"));
		l_entity.setPost_In(rs.getString("Post_In"));
		l_entity.setACStatus(rs.getString("ACStatus"));
		l_entity.setCreatedDate(rs.getString("CreatedDate"));
		l_entity.setUpdatedDate(rs.getString("UpdatedDate"));
		l_entity.setCreatedUser(rs.getLong("CreatedUser"));
		l_entity.setUpdatedUser(rs.getLong("UpdatedUser"));
		l_entity.setCashFlow(rs.getString("CashFlow"));
		l_entity.setDefault_Currency(rs.getLong("Default_Currency"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

	public ArrayList<COACurrency> getInfoList(long coakey) throws SQLException {
		ArrayList<COACurrency> l_COAList = new ArrayList<COACurrency>();
		query = "select  coacu.COACurrencyKey, coacu.CurrencyKey, c.CurrencyCode, c.Description "
				+ "from COA coa "
				+ "inner join COACurrency coacu on coa.COAKey=coacu.COAKey "
				+ "inner join Currency c on coacu.CurrencyKey=c.CurrencyId "
				+ "where coa.COAKey=" + coakey;
		// order by SrNo
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {

				l_COAList.add(readRecords(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_COAList;
	}

	protected COACurrency readRecords(ResultSet rs) throws SQLException {
		COACurrency l_entity = new COACurrency();
		l_entity.setCoacurrencykey(rs.getLong("coacurrencykey"));
		l_entity.setCurrencykey(rs.getLong("currencykey"));
		l_entity.setCurrency_id(rs.getString("currencycode"));
		l_entity.setDescription(rs.getString("description"));
		return l_entity;
	}

	public ResultSet prepareCOAReport(COARpt info) throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_COARpt ?, ?, ?, ? , ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		COARptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}

	private void COARptParameter(CallableStatement l_pstmt, COARpt info)
			throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromACID());
		l_pstmt.setString(i++, info.getToACID());
		l_pstmt.setString(i++, info.getFromDepID());
		l_pstmt.setString(i++, info.getToDepID());
		l_pstmt.setLong(i++, info.getBranchId());
	}

	@Override
	public COADataList getCOAList(COARpt info) throws SQLException {
		COADataList l_DataList = new COADataList();
		ArrayList<COA> l_COAList = new ArrayList<COA>();
		String l_query = "dbo.sp_cr_COARpt ?, ?, ?, ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		COARptParameter(l_pstmt, info);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_COAList.add(readRecordFT(l_rs));
		}
		l_DataList.setEntityList(l_COAList);
		l_rs.close();
		l_pstmt.close();
		return l_DataList;
	}

	private COA readRecordFT(ResultSet rs) throws SQLException {
		COA l_entity = new COA();
		l_entity.setAC_ID(rs.getString("AC_ID"));
		l_entity.setDepartmentid(rs.getString("departmentCode"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setActypeName(rs.getString("ACTypeName"));
		l_entity.setAcgroupName(rs.getString("ACGroupName"));
		return l_entity;
	}

	@Override
	public COA getInfo(COA info) throws SQLException {
		COA l_COA = null;
		query = getSelectQuery() + " where AC_ID = '" + info.getAC_ID() + "'"+" and BranchId = "+ info.getBranchId(); 
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_COA = readRecord1(rs);
		rs.close();
		pstmt.close();
		return l_COA;
	}
	protected COA readRecord1(ResultSet rs) throws SQLException {
		COA l_entity = new COA();
		l_entity.setCOAKey(rs.getLong("COAKey"));
		l_entity.setAC_ID(rs.getString("AC_ID"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setDepartmentKey(rs.getLong("DepartmentKey"));
		l_entity.setACType(rs.getInt("ACType"));
		l_entity.setACGroup(rs.getInt("ACGroup"));
		l_entity.setPost_In(rs.getString("Post_In"));
		l_entity.setACStatus(rs.getString("ACStatus"));
		l_entity.setCreatedDate(rs.getString("CreatedDate"));
		l_entity.setUpdatedDate(rs.getString("UpdatedDate"));
		l_entity.setCreatedUser(rs.getLong("CreatedUser"));
		l_entity.setUpdatedUser(rs.getLong("UpdatedUser"));
		l_entity.setCashFlow(rs.getString("CashFlow"));
		l_entity.setDefault_Currency(rs.getLong("Default_Currency"));
		l_entity.setBranchId(rs.getLong("BranchId"));
		return l_entity;
	}

}
